# encoding: utf-8  
'''
Created on 2018年8月28日
description:数据收集器 for sqlserver
@author: szy
'''
import requests
import time
import json
import sys
sys.path.append('./../helper')
# import InfoMonConstants
# from ConnectionHelper import ConnectionHelper
from helper import InfoMonConstants
from helper.ConnectionHelper import ConnectionHelper
from threading import Thread, Condition

class Collector:
    #收集数据列表
    uploadData = []
    #时间戳
    ts = int(time.time())
    
    def run(self):
        self.collect()
        self.send()

    #连接到sqlserver的连接数量
    def getConnections(self, conn):
        results = conn.getValue("select count(session_id) as allocated,@@max_connections as max_conn \
          from sys.dm_exec_connections")
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "Connections_allocated",
                "timestamp": self.ts,
                "step": 60,
                "value": line[0],
                "counterType": "GAUGE",
                "tags": "name=connections_count",
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "Connections_maxAllow",
                "timestamp": self.ts,
                "step": 60,
                "value": line[1],
                "counterType": "GAUGE",
                "tags": "name=connections_count",
            })

    #活跃active的会话数量
    def getActiveTransaction(self, conn):
        result = conn.getValue("select instance_name,cntr_value from sys.dm_os_performance_counters \
          where object_name = 'SQLServer:Databases'\
          and counter_name = 'Active Transactions' \
          and instance_name not in ('ReportServer','ReportServerTempDB','_Total','mssqlsystemresource','model','msdb')")
        for line in result:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "ActiveTransaction_"+str.strip(line[0]),
                "timestamp": self.ts,
                "step": 60,
                "value": line[1],
                "counterType": "GAUGE",
                "tags": "name=ActiveSession",
            })

    #缓存命中率
    def getBufferHitRatio(self, conn):
        result = conn.getValue("SELECT Convert(decimal(18,2),(a.cntr_value * 1.0 / b.cntr_value) * 100.0) [BufferCacheHitRatio]\
          FROM (SELECT * FROM sys.dm_os_performance_counters\
          WHERE counter_name = 'Buffer cache hit ratio'\
          AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'\
          THEN 'SQLServer:Buffer Manager'\
          ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +\
          ':Buffer Manager' END ) a\
          CROSS JOIN\
          (SELECT * from sys.dm_os_performance_counters\
          WHERE counter_name = 'Buffer cache hit ratio base'\
          and object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'\
          THEN 'SQLServer:Buffer Manager'\
          ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +\
          ':Buffer Manager' END ) b")
        for line in result:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "BufferCacheRatio",
                "timestamp": self.ts,
                "step": 60,
                "value": line[0],
                "counterType": "GAUGE",
                "tags": "name=BufferHitRatio",
            })

    #全表扫描次数
    def getFullScan(self, conn):
        old_result = conn.getValue("select sum(cntr_value) from sys.dm_os_performance_counters where counter_name = 'Full Scans/sec'")
        # print(old_result[0][0])
        time.sleep(1)
        new_result = conn.getValue("select sum(cntr_value) from sys.dm_os_performance_counters where counter_name = 'Full Scans/sec'")
        # print(new_result)
        for line in new_result:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "FullScan",
                "timestamp": self.ts,
                "step": 60,
                "value": line[0] - old_result[0][0],
                "counterType": "GAUGE",
                "tags": "name=FullSeqScan",
            })

    #数据文件占用空间大小
    def getDataSize(self, conn):
        result = conn.getValue("select sum(cntr_value)/1024 from sys.dm_os_performance_counters \
          where object_name like '%SQLServer:Databases%' and counter_name = 'Data File(s) Size (KB)' and instance_name <> '_Total'")
        for line in result:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "DataFileSize",
                "timestamp": self.ts,
                "step": 60,
                "value": line[0],
                "counterType": "GAUGE",
                "tags": "name=DataSize_Mb",
            })

    #刷新日志的等待时间，单位毫秒ms
    def getLogFlushWaittime(self, conn):
        result = conn.getValue("select sum(cntr_value) from sys.dm_os_performance_counters\
          where object_name like '%SQLServer:Databases%' and counter_name = 'Log Flush Wait Time'")
        for line in result:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "LogFlushWaitTime",
                "timestamp": self.ts,
                "step": 60,
                "value": line[0],
                "counterType": "GAUGE",
                "tags": "name=LogFlushWaitTime_ms",
            })

    #数据库tps，每秒事务数
    def getTps(self, conn):
        old_result = conn.getValue("select instance_name,cntr_value from sys.dm_os_performance_counters \
          where object_name like '%SQLServer:Databases%' and counter_name = 'Transactions/sec'\
          and instance_name not in ('ReportServer','ReportServerTempDB','_Total','mssqlsystemresource','model','msdb')")
        time.sleep(1)
        new_result = conn.getValue("select instance_name,cntr_value from sys.dm_os_performance_counters \
          where object_name like '%SQLServer:Databases%' and counter_name = 'Transactions/sec'\
          and instance_name not in ('ReportServer','ReportServerTempDB','_Total','mssqlsystemresource','model','msdb')")
        for i in range(len(new_result)):
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "TPS_" + str.strip(old_result[i][0]),
                "timestamp": self.ts,
                "step": 60,
                "value": new_result[i][1] - old_result[i][1],
                "counterType": "GAUGE",
                "tags": "name=tps",
            })

    #内存使用情况
    def getMemoryInfo(self, conn):
        versionresult = conn.getOne("select @@version")
        version = versionresult[0].split(' ')[3]
        if int(version) > 2008:
            memorySql = "select cpu_count,\
              physical_memory_kb / 1024  as physical_memory_mb,\
              committed_kb * 8 / 1024 as bpool_allocate_mb,\
              committed_target_kb * 8 / 1024 as bpool_target_mb,\
              visible_target_kb * 8 / 1024 as bpool_visible_mb\
              from sys.dm_os_sys_info t"
            result = conn.getValue(memorySql)
            for line in result:
                self.uploadData.append({
                    "endpoint": InfoMonConstants.ENDPOINT,
                    "metric": "cpuCount",
                    "timestamp": self.ts,
                    "step": 60,
                    "value": line[0],
                    "counterType": "GAUGE",
                    "tags": "name=tps",
                })
                self.uploadData.append({
                    "endpoint": InfoMonConstants.ENDPOINT,
                    "metric": "physical_memory_mb",
                    "timestamp": self.ts,
                    "step": 60,
                    "value": line[1],
                    "counterType": "GAUGE",
                    "tags": "name=physical_memory_mb",
                })
                self.uploadData.append({
                    "endpoint": InfoMonConstants.ENDPOINT,
                    "metric": "DBallocate_mb",
                    "timestamp": self.ts,
                    "step": 60,
                    "value": line[2],
                    "counterType": "GAUGE",
                    "tags": "name=DBallocate_mb",
                })
        elif int(version) == 2008:
            memorySql = "select cpu_count,\
              physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,\
              bpool_committed * 8 / 1024 as bpool_allocate_mb,\
              bpool_commit_target * 8 / 1024 as bpool_target_mb,\
              bpool_visible * 8 / 1024 as bpool_visible_mb\
              from sys.dm_os_sys_info t"
            result = conn.getValue(memorySql)
            for line in result:
                self.uploadData.append({
                    "endpoint": InfoMonConstants.ENDPOINT,
                    "metric": "cpuCount",
                    "timestamp": self.ts,
                    "step": 60,
                    "value": line[0],
                    "counterType": "GAUGE",
                    "tags": "name=tps",
                })
                self.uploadData.append({
                    "endpoint": InfoMonConstants.ENDPOINT,
                    "metric": "physical_memory_mb",
                    "timestamp": self.ts,
                    "step": 60,
                    "value": line[1],
                    "counterType": "GAUGE",
                    "tags": "name=physical_memory_mb",
                })
                self.uploadData.append({
                    "endpoint": InfoMonConstants.ENDPOINT,
                    "metric": "DBallocate_mb",
                    "timestamp": self.ts,
                    "step": 60,
                    "value": line[2],
                    "counterType": "GAUGE",
                    "tags": "name=DBallocate_mb",
                })
        else:
            print("coming soon......")

    def getIoWait(self, conn):
        result = conn.getValue("select 'PAGEIOLATCH', sum(waiting_tasks_count), sum(wait_time_ms),\
          sum(wait_time_ms) / sum(waiting_tasks_count) as avg_waittime_ms\
          from sys.dm_os_wait_stats\
          where wait_type like 'PAGEIOLATCH%'  and waiting_tasks_count > 0")
        for line in result:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "avg_waittime_ms",
                "timestamp": self.ts,
                "step": 60,
                "value": line[3],
                "counterType": "GAUGE",
                "tags": "name=avg_waittime_ms",
            })

    def getLockSession(self, conn):
        result = conn.getValue("select blocking_session_id as blockor_session_id, wait_duration_ms, session_id as blocked_session_id from\
          sys.dm_os_waiting_tasks\
          where blocking_session_id is not null")
        for line in result:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "LockSession_time",
                "timestamp": self.ts,
                "step": 60,
                "value": line[1],
                "counterType": "GAUGE",
                "tags": "name=LockSession_time_ms",
            })

    #数据收集
    def collect(self):
        connHelper = ConnectionHelper(InfoMonConstants.DBNAME)

        #for thread
        threads = []
        t1 = Thread(target=self.getConnections, args=(connHelper,))
        t2 = Thread(target=self.getActiveTransaction, args=(connHelper,))
        t3 = Thread(target=self.getBufferHitRatio, args=(connHelper,))
        t4 = Thread(target=self.getFullScan, args=(connHelper,))
        t5 = Thread(target=self.getDataSize, args=(connHelper,))
        t6 = Thread(target=self.getLogFlushWaittime, args=(connHelper,))
        t7 = Thread(target=self.getTps, args=(connHelper,))
        t8 = Thread(target=self.getMemoryInfo, args=(connHelper,))
        t9 = Thread(target=self.getIoWait, args=(connHelper,))
        t10 = Thread(target=self.getLockSession, args=(connHelper,))

        threads.append(t1)
        threads.append(t2)
        threads.append(t3)
        threads.append(t4)
        threads.append(t5)
        threads.append(t6)
        threads.append(t7)
        threads.append(t8)
        threads.append(t9)
        threads.append(t10)

        for t in threads:
            # t.setDaemon(True)
            t.start()
        for t in threads:
            t.join()
        #end thread

        for line in self.uploadData:
            print(line)

        connHelper.closeConn()
    
    #发送数据到transfer
    def send(self):
        if 0 != len(self.uploadData):
            r = requests.post(InfoMonConstants.FALCON_CLINENT, data=json.dumps(self.uploadData))
            print(r.text)



